package DBLayer;

import java.sql.*;
import java.util.ArrayList;

public class DBLocation implements IFDBLocation {
	private Connection con;
	private static DBLocation dbLoc;
	
	private DBLocation() {
		con = DBConnection.getInstance().getDBcon();
	}
	
	public static DBLocation getInstance(){
		if(dbLoc==null) dbLoc = new DBLocation();
		return dbLoc;
	}

	@Override
	public Object[] getLocation(int locationId){
		return singleWhere("locationId="+locationId);
	}
	
	@Override
	public Object[] getLocation_byCity(String city){
		return singleWhere("city LIKE '"+city+"%'");
	}
	
	@Override
	public Object[] getLocation_byPostalCode(String postalCode){
		return singleWhere("postalCode LIKE '"+postalCode+"%'");
	}
	
	
	
	
	@Override
	public ArrayList<Object[]> getAllLocations() {
		return miscWhere("");
	}

	@Override
	public ArrayList<Object[]> getAllLocations_byPostalCode(String postS) {
		return miscWhere("postalCode='"+postS+"'");
	}

	@Override
	public ArrayList<Object[]> getAllLocations_byCity(String cityS) {
		return miscWhere("city='"+cityS+"'");
	}

	@Override
	public ArrayList<Object[]> getAllLocations_byCountry(String countryS) {
		return miscWhere("country='"+countryS+"'");
	}

	@Override
	public int insert(String postalCode, String city, String country) {
		int rc = -1;
		String check  = "SELECT locationId FROM Location WHERE postalCode='"+postalCode+"' AND country='"+country+"'";
		String updateAfterCheck = "UPDATE Location SET city='"+city+"' WHERE postalCode='"+postalCode+"' AND country='"+country+"'";
		String insert = "INSERT INTO Location (postalCode,city,country) VALUES ('"+postalCode+"','"+city+"','"+country+"')";
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			
			ResultSet result = stmt.executeQuery(check);
			if(result.next())
			{
				rc = result.getInt("locationId");
				stmt.executeUpdate(updateAfterCheck);
			}
			else
			{
				rc = stmt.executeUpdate(insert, Statement.RETURN_GENERATED_KEYS);
				
				ResultSet rs = stmt.getGeneratedKeys();
		        if (rs.next())  rc = rs.getInt(1);
		        rs.close();
			}
	        
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not insert the location  "+e.getMessage());
		}
		
		
		return rc;
	}
	

	
	@Override
	public int update(Object[] location) {
		int rc = -1;
		String query = "UPDATE Location SET postalCode='"+location[1]+"',city='"+location[2]+"',country='"+location[3]+"' WHERE locationId="+location[0];
		
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not update the location "+e.getMessage());
		}
		
		return rc;
	}

	@Override
	public int delete(int locationId) {
		int rc = -1;
		String query = "DELETE FROM Location WHERE locationId="+locationId;
		
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not delete the location "+e.getMessage());
		}
		
		return rc;
	}
	
	
	
	
	
	
	
	
	
	
	
	
	private Object[] singleWhere(String wClause){
		Object[] location = new String[4];
		
		ResultSet result;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			result = stmt.executeQuery(query);
			
			if(result.next()){
				location = buildLocation(result);
			}
			
			stmt.close();
			
		}
		catch(Exception e){
			System.err.println("Could not execute select location query "+e.getMessage());
		}
		
		return location;
	}
	
	
	private ArrayList<Object[]> miscWhere(String wClause){
		ArrayList<Object[]> locations = new ArrayList<>();
		
		ResultSet results;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			while(results.next())
			{
				locations.add(buildLocation(results));
			}
			
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not execute multiple select location query "+e.getMessage());
		}
		
		return locations;
	}
	
	
	
	private String buildQuery(String wClause){
		
		String query = "SELECT locationId,postalCode,city,country FROM Location";
		
		if(wClause.length() > 0) query += " WHERE "+wClause;
		
		return query;		
	}

	private Object[] buildLocation(ResultSet result) {
		Object[] location = new String[4];
		try {
			location[0] = String.valueOf(result.getInt("locationId"));
			location[1] = result.getString("postalCode");
			location[2] = result.getString("city");
			location[3] = result.getString("country");
		} catch (Exception e) {
			System.err.println("Could not build the Location Result "+e.getMessage());
		}
		return location;
	}



}
